package cn.com.dashihui.api.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.api.base.BaseService;
import cn.com.dashihui.api.common.GoodCode;
import cn.com.dashihui.api.common.OrderCode;

public class GoodsService  extends BaseService{
	
	/**
	 * 根据优惠类型查询各类型一件商品
	 * @param type 优惠类型，1：普通，2：推荐，3：限量，4：一元购
	 * @param storeid 店铺ID
	 */
	public Record topByType(int type,String storeid){
		
		String sql = "SELECT g.id,g.name,g.thumb,g.spec,g.marketPrice,g.sellPrice,g.urv,g.isSelf "
				+ " FROM t_bus_goods g "
				+ "WHERE g.state=1 AND g.type=? AND ORDER BY rand()";
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			sql="SELECT g.id,g.name,g.thumb,g.spec,g.marketPrice,g.sellPrice,g.urv,g.isSelf "
					+ " FROM t_bus_goods g "
					+ "WHERE g.state=1 AND g.type=? AND g.good_store in (select tbgsr.goodstore from t_bus_goods_store_rel tbgsr where tbgsr.storeid = ? ) AND ORDER BY rand()";
			return Db.findFirst(sql,type,storeid);
		}
		return Db.findFirst(sql,type);
	}
	
	/**
	 * 查询商品列表
	 * @param storeid 店铺ID
	 * @param categoryCode 主分类代码
	 * @param categorytwCode 二级分类代码
	 * @param type 优惠类型
	 * @param pageNum 页码
	 * @param pageSize 数量
	 * @param ORDERBY 排序，1：默认排序，2：销量从高到低，3：价格从低到高，4：价格从高到低
	 */
	public Page<Record> findByPage(int storeid,String categoryonCode,String categorytwCode,int type,int pageNum,int pageSize,int orderBy){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.spec,g.marketPrice,g.sellPrice,g.urv,g.isSelf,g.isRebate ";
		StringBuffer sqlFromSelect = new StringBuffer("FROM t_bus_goods g");
		StringBuffer sqlWhereSelect = new StringBuffer(" WHERE g.state=1 AND g.storeid=?");
		List<Object> params = new ArrayList<Object>();
		params.add(storeid);
		//判断优惠类型不为空且不为1：普通类型时
		if(type!=0&&type!=1){
			sqlWhereSelect.append(" AND g.type=?");
			params.add(type);
		}
		//判断如果分类不为空，则添加条件
		if(!StrKit.isBlank(categoryonCode)){
			sqlFromSelect.append(" INNER JOIN t_dict_category c1 ON g.categoryonid=c1.categoryId");
			sqlWhereSelect.append(" AND c1.categoryNum=?");
			params.add(categoryonCode);
		}
		if(!StrKit.isBlank(categorytwCode)){
			sqlFromSelect.append(" INNER JOIN t_dict_category c2 ON g.categorytwid=c2.categoryId");
			sqlWhereSelect.append(" AND c2.categoryNum=?");
			params.add(categorytwCode);
		}
		//根据排序要求，作不同处理
		if(orderBy==1){
		}else if(orderBy==2){
			sqlSelect = sqlSelect + ",(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=g.id AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";
			sqlWhereSelect.append(" ORDER BY saleCount DESC");
		}else if(orderBy==3){
			sqlWhereSelect.append(" ORDER BY g.sellPrice ASC");
		}else if(orderBy==4){
			sqlWhereSelect.append(" ORDER BY g.sellPrice DESC");
		}
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlFromSelect.append(sqlWhereSelect).toString(), params.toArray());
	}
	/**
	 * 查询普通商品
	 * @param oneid
	 * @param twoid
	 * @param threeid
	 * @param pageNum
	 * @param pageSize
	 * @param orderBy
	 * @param storeid
	 * @param cateId
	 * @return
	 */
	public Page<Record> findByPage_v133(String oneid,String twoid,String threeid,int pageNum,int pageSize,int orderBy,String storeid,int cateId){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.categorythid,g.categoryfoid,ts.saleprice,tc.categoryName ";
		StringBuffer sqlFromSelect = new StringBuffer("FROM t_bus_goods g LEFT JOIN (SELECT * FROM t_bus_goods_sale GROUP BY goodsid) ts ON ts.goodsid=g.id LEFT JOIN t_dict_category tc ON tc.categoryId = g.categoryfoid ");
		StringBuffer sqlWhereSelect = new StringBuffer(" WHERE g.state=2 AND g.groupbuying=? ");
		List<Object> params = new ArrayList<Object>();
		params.add(GoodCode.ISGROUP.NOTGROUP);
		sqlWhereSelect.append(" AND g.categoryonid=?");
		params.add(oneid);
		sqlWhereSelect.append(" AND g.categorytwid=?");
		params.add(twoid);
		sqlWhereSelect.append(" AND g.categorythid=?");
		params.add(threeid);
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			sqlWhereSelect.append(" AND g.good_store in (select tbgsr.goodstore from t_bus_goods_store_rel tbgsr where tbgsr.storeid = ? )");
			params.add(storeid);
		}
		//根据排序要求，作不同处理
		if(orderBy==1){
			sqlSelect = sqlSelect + ",(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=g.id AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";
		}else if(orderBy==2){
			sqlSelect = sqlSelect + ",(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=g.id AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";
			sqlWhereSelect.append(" ORDER BY saleCount DESC");
		}else if(orderBy==3){
			sqlWhereSelect.append(" ORDER BY ts.saleprice ASC");
		}else if(orderBy==4){
			sqlWhereSelect.append(" ORDER BY ts.saleprice DESC");
		}
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlFromSelect.append(sqlWhereSelect).toString(), params.toArray());
	}
	/**
	 * 查询兑换商品
	 * @param oneid
	 * @param twoid
	 * @param threeid
	 * @param pageNum
	 * @param pageSize
	 * @param orderBy
	 * @param storeid
	 * @param cateId
	 * @return
	 */
	public Page<Record> findByPage_v134(String oneid,String twoid,String threeid,int pageNum,int pageSize,int orderBy,String storeid,int cateId){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.categorythid,g.categoryfoid,tc.categoryName,ts.specNum,ts.marketPrice,ts.stockNum,ts.multipay,ts.id AS sid, "
				+ "ts.opPoint,ts.opDedu,ts.opCoupon,ts.muchsellerPrice,ts.mpPoint,ts.mpDedu,ts.mpCoupon ";
		StringBuffer sqlFromSelect = new StringBuffer("FROM t_bus_ege_goods g LEFT JOIN (SELECT * FROM t_bus_ege_goods_spec GROUP BY goodId) ts ON ts.goodId=g.id LEFT JOIN t_dict_category tc ON tc.categoryId = g.categoryfoid ");
		StringBuffer sqlWhereSelect = new StringBuffer(" WHERE 1=1  AND g.state=2 ");
		List<Object> params = new ArrayList<Object>();
		sqlWhereSelect.append(" AND g.categoryonid=?");
		params.add(oneid);
		sqlWhereSelect.append(" AND g.categorytwid=?");
		params.add(twoid);
		sqlWhereSelect.append(" AND g.categorythid=?");
		params.add(threeid);
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			sqlWhereSelect.append(" AND g.good_store in (select tbgsr.goodstore from t_bus_goods_store_rel tbgsr where tbgsr.storeid = ? )");
			params.add(storeid);
		}
		//根据排序要求，作不同处理
		if(orderBy==1){
			sqlSelect = sqlSelect + ",(SELECT COUNT(*) FROM t_bus_order_act bo  WHERE 1=1 AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";
		}else if(orderBy==2){
			sqlSelect = sqlSelect + ",(SELECT COUNT(*) FROM t_bus_order_act bo  WHERE 1=1 AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";
			sqlWhereSelect.append(" ORDER BY saleCount DESC");
		}else if(orderBy==3){
			sqlWhereSelect.append(" ORDER BY ts.saleprice ASC");
		}else if(orderBy==4){
			sqlWhereSelect.append(" ORDER BY ts.saleprice DESC");
		}
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlFromSelect.append(sqlWhereSelect).toString(), params.toArray());
	}
	/**
	 * 查询所有普通商品
	 * @param keyWord
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public Page<Record> findByPageAll(int pageNum,int pageSize,String keyWord){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,ts.saleprice,"
				+ "(SELECT COUNT(bo.id) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=g.id AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";
		StringBuffer sqlFromSelect = new StringBuffer("FROM t_bus_goods g LEFT JOIN (SELECT * FROM t_bus_goods_sale GROUP BY goodsid) ts ON ts.goodsid=g.id ");
		StringBuffer sqlWhereSelect = new StringBuffer(" WHERE g.state=2 ");
		List<Object> params = new ArrayList<Object>();
		sqlWhereSelect.append(" AND g.groupbuying=?");
		params.add(GoodCode.ISGROUP.NOTGROUP);
		sqlWhereSelect.append(" AND g.name like ?");
		params.add("%"+keyWord+"%");
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlFromSelect.append(sqlWhereSelect).toString(), params.toArray());
	}
	/**
	 * 查询所有兑换商品
	 * @param keyWord
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public Page<Record> findByPageAllAct(int pageNum,int pageSize,String keyWord){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.categorythid,g.categoryfoid,tc.categoryName,ts.specNum,ts.marketPrice,ts.stockNum,ts.multipay,ts.id AS sid, "
				+ "ts.opPoint,ts.opDedu,ts.opCoupon,ts.muchsellerPrice,ts.mpPoint,ts.mpDedu,ts.mpCoupon, "
				+ "(SELECT COUNT(bo.id) FROM t_bus_order_act bo WHERE bo.goodsid=g.id AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";

		StringBuffer sqlFromSelect = new StringBuffer("FROM t_bus_ege_goods g LEFT JOIN (SELECT * FROM t_bus_ege_goods_spec GROUP BY goodId) ts ON ts.goodId=g.id LEFT JOIN t_dict_category tc ON tc.categoryId = g.categoryfoid ");
		StringBuffer sqlWhereSelect = new StringBuffer(" WHERE 1=1  AND g.state=2 ");
		List<Object> params = new ArrayList<Object>();
		sqlWhereSelect.append(" AND g.name like ?");
		params.add("%"+keyWord+"%");
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlFromSelect.append(sqlWhereSelect).toString(), params.toArray());
	}
	/**
	 * 查询所有兑换商品
	 * @param keyWord
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	/*public Page<Record> findByPageAllAct(int pageNum,int pageSize,String keyWord){
		String sqlSelect = "SELECT  g.id,g.name,g.thumb,ts.saleprice,1 as type, "
				+ " (SELECT COUNT(bo.id) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=g.id AND bo.orderState=2) "
				+ " AS saleCount FROM t_bus_goods g LEFT JOIN (SELECT * FROM t_bus_goods_sale GROUP BY goodsid) "
				+ " ts ON ts.goodsid=g.id WHERE g.state=2 UNION ALL SELECT eg.id,eg.name,eg.thumb,tg.marketPrice,2 as type, "
				+ " (SELECT COUNT(bo.id) FROM t_bus_order_act bo INNER JOIN t_bus_order_list bol "
				+ " ON bo.orderNum=bol.orderNum WHERE bol.goodsid=eg.id AND bo.orderState=2) as saleCount "
				+ " FROM t_bus_ege_goods eg LEFT JOIN (SELECT * FROM t_bus_ege_goods_spec GROUP BY goodId) "
				+ " tg ON tg.goodId=eg.id WHERE eg.state=2 ";
		StringBuffer sBuffer = new StringBuffer("");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND g.groupbuying=?");
		params.add(GoodCode.ISGROUP.NOTGROUP);
		sBuffer.append(" AND g.name like ?");
		params.add("%"+keyWord+"%");
		return Db.paginate(pageNum, pageSize, sqlSelect, sBuffer.toString(), params.toArray());
	}*/
	
	
	/**
	 * 查询指定店铺的所有可用且可显示的商品标签，以及各标签下N条商品数据
	 * @param storeid
	 * @param max 要随标签显示的商品最大数量
	 */
	public List<Record> getAllTag(int storeid,int max){
		List<Record> list = Db.find("SELECT gt.tagName,gt.code tagCode,gt.orderNo,g.id,g.name,g.thumb,g.marketPrice,g.sellPrice"
				+ " FROM t_bus_goods g"
				+ " INNER JOIN t_bus_goods_tag_rel gtr ON g.id=gtr.goodsid"
				+ " INNER JOIN t_bus_goods_tag gt ON gtr.tagid=gt.id AND gt.storeid=?"
				+ " WHERE g.state=1 AND g.storeid=? AND gt.enabled=1 AND gt.isShow=1"
				+ " ORDER BY gt.orderNo",storeid,storeid);
		if(list!=null){
			Map<String,Record> tags = new LinkedHashMap<String,Record>();
			for(Record row : list){
				String tagCode = row.get("TAGCODE");
				Record tag = tags.get(tagCode);
				List<Record> goodsList;
				if(tag==null){
					goodsList = new ArrayList<Record>();
					tags.put(tagCode, new Record()
							.set("TAGNAME", row.get("TAGNAME"))
							.set("TAGCODE", row.get("TAGCODE"))
							.set("LIST", goodsList));
				}else{
					goodsList = tag.get("LIST");
					//查询的商品数量如果大于最大值，则跳过
					if(goodsList.size()>=max){
						continue;
					}
				}
				goodsList.add(row);
			}
			return Lists.newArrayList(tags.values().iterator());
		}
		return null;
	}
	
	/**
	 * 查询指定标签的商品列表
	 * @param storeid 店铺ID
	 * @param tagCode 标签代码
	 * @param pageNum 页码
	 * @param pageSize 数量
	 */
	public Page<Record> findPageByTag(int storeid,String tagCode,int pageNum,int pageSize){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.marketPrice,g.sellPrice,g.isSelf ";
		String sqlWhere = " FROM t_bus_goods g"
				+ " INNER JOIN t_bus_goods_tag_rel gtr ON g.id=gtr.goodsid"
				+ " INNER JOIN t_bus_goods_tag gt ON gtr.tagid=gt.id AND gt.storeid=?"
				+ " WHERE g.state=1 AND g.storeid=? AND gt.code=? AND gt.enabled=1 AND gt.isShow=1";
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlWhere, storeid, storeid, tagCode);
	}
	
	/**
	 * 查询精品推荐的商品列表
	 * @param storeid 店铺ID
	 * @param pageNum 页码
	 * @param pageSize 数量
	 */
	public Page<Record> findPageByRecom(int storeid,int pageNum,int pageSize){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.marketPrice,g.sellPrice,g.isSelf ";
		String sqlWhere = " FROM t_bus_goods g"
				+ " WHERE g.state=1 AND (g.storeid=? OR g.isSelf=1) AND g.type=2";
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlWhere, storeid);
	}
	
	/**
	 * 查询商品信息
	 * @param storeid 店铺ID
	 *
	 */
	public Record findDetail(int goodsid,String storeid){
		String sql = "SELECT tg.id,tg.Qrthumb,tg.name,tg.sellPrice,tg.buytype,tg.buycount,tg.shiyongren,tg.regul,tg.shortInfo,"
				+ "tg.zspointnum,tg.zsdedunum,tg.zscouponnum,tg.zsactivity,tg.browse,tg.gbmethod,tg.gbfreeperson,tg.gbpayperson, "
				+ "tg.thumb,tg.state,tg.describe,tg.LunBoNum,tg.groupbuying,tg.gbperson,tg.gbprice,tg.gbbegintime,tg.gbendtime, "
				+ "0 isCollected,"
				+ "(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=? AND bo.payState=?) saleCount,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.goodsid=? AND buc.isCancel=0) collectedCount "
				+ " FROM t_bus_goods tg "
				+ " WHERE  tg.id=? ";
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			 sql = sql +"AND tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ";
			return Db.findFirst(sql,goodsid,OrderCode.OrderState.FINISH,goodsid,goodsid,storeid);
		}
		return Db.findFirst(sql,goodsid,OrderCode.OrderState.FINISH,goodsid,goodsid);
	}
	
	/**
	 * 查询充值卡信息
	 * @param goodsid 充值卡ID
	 *
	 */
	public Record findDetail1(int goodsid){
		return Db.findFirst("SELECT * FROM t_bus_goods_ecard  WHERE  id=? and pstate=1 and state =2 and countnum >0",goodsid);
	}
	
	/**
	 * 查询商品信息，并且查询该商品是否被当前登录用户收藏
	 * @param userid 当前登录用户ID
	 * 
	 * @param goodsid 商品ID
	 */
	public Record findDetail(int userid,int goodsid,String storeid){
		String sql = "SELECT tg.id,tg.Qrthumb,tg.name,tg.sellPrice,tg.buytype,tg.buycount,tg.shiyongren,tg.regul,tg.shortInfo,"
				+ "tg.zspointnum,tg.zsdedunum,tg.zscouponnum,tg.zsactivity,tg.browse, "
				+ "tg.thumb,tg.state,tg.describe,tg.LunBoNum,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND buc.goodsid=? AND buc.isCancel=0)!=0 isCollected,"
				+ "(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=? AND bo.payState=?) saleCount,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.goodsid=? AND buc.isCancel=0) collectedCount "
				+ " FROM t_bus_goods tg "
				+ " WHERE  tg.id=? ";
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			 sql = sql +"AND tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ";
			return Db.findFirst(sql,userid,goodsid,OrderCode.OrderState.FINISH,goodsid,goodsid,storeid);
		}
		return Db.findFirst(sql,userid,goodsid,OrderCode.OrderState.FINISH,goodsid,goodsid);
	}
	
	/**
	 * 查询商品图片集
	 * @param goodsid 商品ID
	 */
	public List<Record> findImages(String LunBoNum){
		return Db.find("SELECT thumb FROM t_bus_goods_base_images sgi WHERE LunBoNum=? ORDER BY orderNo",LunBoNum);
	}
	/**
	 * 查询商品规格集
	 * @param goodsid 商品ID
	 */
	public List<Record> findSpec(int goodsid){
		String sql = "SELECT goodsid,id,title,goodsSpec,saleprice,salestock,coding,barcode "
				+ "FROM t_bus_goods_sale  "
				+ "WHERE goodsid=? ORDER BY saleprice ASC";
		
		return Db.find(sql,goodsid);
	}
	public List<Record> findComSpec(int goodsid){
		String sql = "SELECT `iname`,`idetail` "
				+ "FROM t_bus_goods_sale_items  "
				+ "WHERE goodsid=?";
		return Db.find(sql,goodsid);
	}
	
	/**
	 * 查询商品规格集
	 * @param goodsid 商品ID
	 */
	public List<Record> findDetail(String LunBoNum){
		return Db.find("SELECT gdi.thumb FROM goods_details_images gdi WHERE LunBoNum=? ORDER BY orderNo",LunBoNum);
	}
	/**
	 * 查询商品图文详情
	 * @param goodsid 商品ID
	 */
	public List<Record> findDetailDescribe(int goodsid,String storeid){
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			String sql="SELECT gdi.thumb,gdi.orderNo "
					+ "FROM goods_details_images gdi , t_bus_goods tg "
					+ "WHERE tg.LunBoNum=gdi.LunBoNum AND tg.id=? AND tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ORDER BY gdi.orderNo";
			return Db.find(sql,goodsid,storeid);
		}
		return Db.find("SELECT gdi.thumb,gdi.orderNo FROM goods_details_images gdi , t_bus_goods tg WHERE tg.LunBoNum=gdi.LunBoNum AND tg.id=? ORDER BY gdi.orderNo",goodsid);
	}
	
	/**
	 * 秒杀今日列表查询
	 */
	public List<Record> findSpikeList() {
		// TODO Auto-generated method stub
		String sql = "SELECT  sl.id,sl.timeid "
				+ "FROM t_spike_list sl "
				+ "WHERE sl.publish!=0  AND sl.start<=? AND sl.stop>=?";
		Date date = new Date();
		return Db.find(sql,date,date);
		
	}
	/**
	 * 秒杀详细信息
	 * @param record
	 * @return
	 */
	public Map<String,List<Record>> findGoodsByid(Map<String,List<Record>> map,Record record,String storeid) {
		// TODO Auto-generated method stub
		String timeid = record.get("timeid");
		String[] timeids = timeid.split(",");
		String sql1 = "SELECT "
				+ "st.begin,st.end "
				+ "FROM t_spike_time st "
				+ "WHERE id=? AND st.state!=0  ";
		String sql = "SELECT "
				+ "sg.id,su.spikePrice,su.spikeNum,su.limited,su.spikeLeft, "
				+ "sg.sellPrice,sg.shortInfo,sg.spec,sg.thumb,sg.name,ts.saleprice,ts.id AS sid "
				+ " ,(SELECT IFNULL(SUM(bol.count),0 ) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=sg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY+") saleCount "
				+ "FROM t_spike_union su "
				+ "LEFT JOIN t_bus_goods sg ON su.goodsId = sg.id "
				+ "LEFT JOIN (SELECT t.saleprice,t.id,t.goodsId FROM t_bus_goods_sale t  GROUP BY t.goodsId ) ts ON ts.goodsId = su.goodsId "
				+ "WHERE 1=1  AND su.listId = ?  "
				+ "AND sg.state=2 AND sg.groupbuying=1 ORDER BY su.orderNo ASC";
	
		int id = record.getInt("id");
		List<Record> re = Db.find(sql,id);
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			sql = "SELECT "
					+ "sg.id,su.spikePrice,su.spikeNum,su.limited,su.spikeLeft, "
					+ "sg.sellPrice,sg.shortInfo,sg.spec,sg.thumb,sg.name,ts.saleprice,ts.id AS sid "
					+ " ,(SELECT IFNULL(SUM(bol.count),0 ) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=sg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY+") saleCount "
					+ "FROM t_spike_union su "
					+ "LEFT JOIN t_bus_goods sg ON su.goodsId = sg.id "
					+ "LEFT JOIN (SELECT t.saleprice,t.id,t.goodsId FROM t_bus_goods_sale t  GROUP BY t.goodsId ) ts ON ts.goodsId = su.goodsId "
					+ "WHERE 1=1  AND su.listId = ?  "
					+ "AND sg.state=2 "
					+ "AND sg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) "
					+ "ORDER BY su.orderNo ASC";
			re= Db.find(sql,id,storeid);
		}
		for(int i=0;i<timeids.length;i++){
			Record time = Db.findFirst(sql1,timeids[i]);
			Object object = time.get("begin");
			String s = object.toString();
			List<Record> core =  map.get(s.substring(0, 2));
			if(re.size()>0&&re!=null){
				String ss = null;
				if(s.substring(0, 1).equals("0")){
					 ss= s.substring(1, 2);
				}else{
					ss = s.substring(0, 2);
				}
				if(core==null){
					map.put(ss,re);
				}else{
					core.addAll(re);
					map.put(ss,core);
				}
			}
			
		}
		return map;
	}
	/**
	 * 
	 * @param gp
	 * 链接到所属分类的外键,,
	 */
	public List<Record> findFresh(String gp,String storeid, int type) {
		// TODO Auto-generated method stub
		
		List<Record> list = null;
		List<Record> list1 = new ArrayList<Record>();
		if(gp.equals("1")){
			//品牌直供   fkey链接到t_bus_seller
			String sql = "SELECT "
					+ "tr.authority,tr.orderNo, "
					+ "FROM t_spike_recommend tr "
					+ "INNER JOIN t_bus_seller ts ON tr.fkey=ts.id "
					+ "WHERE 1=1 AND tr.recommend=1 AND tr.gp=1  ORDER BY tr.orderNo ASC ";
			list1 = Db.find(sql , gp);
		}else if (gp.equals("10")) {
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT distinct ");
			sb.append("tg.thumb,tg.name,tg.id,tg.shortInfo,tg.sellPrice,tg.gbprice, ");
			sb.append("( ");
			sb.append("SELECT IFNULL(SUM(bol.count),0 ) ");
			sb.append("FROM t_bus_order_gb bo ");
			sb.append("INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum ");
			sb.append("WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY);
			sb.append(") saleCount ");
			sb.append("FROM t_spike_recommend tr ");
			sb.append("LEFT JOIN  t_bus_goods tg ON (tr.fkey = tg.id AND tr.goodsType = 1 AND tg.groupbuying=? AND tg.state=2) ");
			sb.append("WHERE 1=1 ");
			sb.append("AND tg.gbendtime > now() AND tg.gbbegintime<= now() ");
			if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
				sb.append("AND tg.good_store in ");
				sb.append("(select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ");
				list1 = Db.find(sb.toString(), GoodCode.ISGROUP.GROUP,storeid);
				
			}else{
				list1 = Db.find(sb.toString(), GoodCode.ISGROUP.GROUP);
				
			}
			
			
//			//拼团专区10      链接到t_bus_goods
//			list=Db.find("select authority,orderNo,goodsType,state,fkey from t_spike_recommend where recommend=1 AND gp=? ORDER BY orderNo ASC", gp);
//			for (Record record : list) {
//				Record r=new Record();
//				if (record.getInt("goodsType")==1) {
//					String sql="";
//					if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
//						sql="SELECT "
//							+ "tg.thumb,tg.name,tg.id,tg.shortInfo,tg.sellPrice,tg.gbprice,"
//							+ "( "
//							+ "SELECT IFNULL(SUM(bol.count),0 ) "
//							+ "FROM t_bus_order_gb bo "
//							+ "INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum "
//							+ "WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY
//							+ ") saleCount "
//							+ "FROM t_bus_goods tg "
//							+ "WHERE 1=1 AND tg.groupbuying=? AND tg.id=? AND tg.state=2 "
//							+ "AND gbendtime > now() AND gbbegintime<= now() "
//							+ "AND tg.good_store in "
//							+ "(select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ";
//						 r= Db.findFirst(sql, GoodCode.ISGROUP.GROUP, record.getInt("fkey"),storeid);
//					}else {
//						sql = "SELECT "
//							+ "tg.thumb,tg.name,tg.id,tg.shortInfo,tg.sellPrice,tg.gbprice, "
//							+ "( "
//							+ "SELECT IFNULL(SUM(bol.count),0 ) "
//							+ "FROM t_bus_order_gb bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum "
//							+ "WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY
//							+") saleCount "
//							+ "FROM  t_bus_goods tg "
//							+ "WHERE 1=1 AND tg.groupbuying=? AND tg.id=? AND tg.state=2 "
//							+ "AND gbendtime > now() AND gbbegintime<= now() ";
//						r = Db.findFirst(sql , GoodCode.ISGROUP.GROUP, record.getInt("fkey"));
//					}
//				}
//				if (r!=null) {
//					record.setColumns(r);
//					list1.add(record);
//				}
//			}
		}else /*if (gp.equals("2"))*/ {
			//综合商品 2      链接到t_bus_goods和t_bus_eve_goods
			//人气推荐3        链接到t_bus_goods和t_bus_eve_goods
			//专题精选4	暂时链接到t_bus_goods和t_bus_eve_goods
			//亿品5        链接到t_bus_goods和t_bus_eve_goods
			//传媒6        链接到t_bus_goods和t_bus_eve_goods
			//优品7        链接到t_bus_goods和t_bus_eve_goods
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT distinct ");
			sb.append("tg.thumb,tg.name,tg.id,tg.shortInfo,tg.sellPrice,ts.saleprice, ");
			sb.append("( ");
			sb.append("SELECT IFNULL(SUM(bol.count),0 ) ");
			sb.append("FROM t_bus_order bo ");
			sb.append("INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum ");
			sb.append("WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY);
			sb.append(") saleCount ");
			sb.append("FROM t_bus_goods tg ");
			sb.append("RIGHT JOIN  t_spike_recommend tr ON (tr.fkey = tg.id AND tr.goodsType = 1 AND gp=? ) ");
			sb.append("LEFT JOIN (SELECT t.saleprice,t.goodsId FROM t_bus_goods_sale t GROUP BY t.goodsId) ts ON ts.goodsId=tg.id ");
			sb.append("WHERE 1=1 AND tg.groupbuying=? AND tg.state=2 ");
			
			if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
				sb.append("AND tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ");
				if(type == 1){
					sb.append("AND tr.recommend=? ");
					list1 = Db.find(sb.toString(), gp, GoodCode.ISGROUP.NOTGROUP,storeid,type);
				}else{
					list1 = Db.find(sb.toString(), gp, GoodCode.ISGROUP.NOTGROUP,storeid);
				}
			}else{
				if(type == 1){
					sb.append("AND tr.recommend=? ");
					list1 = Db.find(sb.toString(), gp, GoodCode.ISGROUP.NOTGROUP, type);
				}else{
					list1 = Db.find(sb.toString(), gp, GoodCode.ISGROUP.NOTGROUP);
				}
			}
			
			sb = new StringBuffer();
			sb.append("SELECT distinct ");
			sb.append("tg.thumb,tg.name,tg.shortInfo,tg.sellPrice,tgs.*, ");
			sb.append("( ");
			sb.append("SELECT IFNULL(SUM(bol.count),0 ) ");
			sb.append("FROM t_bus_order_act bo ");
			sb.append("INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum ");
			sb.append("WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY);
			sb.append(") saleCount ");
			sb.append("FROM t_bus_ege_goods tg ");
			sb.append("RIGHT JOIN  t_spike_recommend tr ON (tr.fkey = tg.id AND tr.goodsType = 2 AND gp=? ) ");
			sb.append("LEFT JOIN t_bus_ege_goods_spec tgs ON tgs.goodId=tg.id ");
			sb.append("WHERE 1=1  AND tg.state=2 ");
			if(type == 1){
				sb.append("AND tr.recommend=? ");
				list1.addAll(Db.find(sb.toString(), gp, type));
			}else{
				list1.addAll(Db.find(sb.toString(), gp));
			}
//			list=Db.find("select authority,orderNo,goodsType,state,fkey from t_spike_recommend where recommend=1 AND gp=? ORDER BY orderNo ASC", gp);
//			for (Record record : list) {
//				Record r=null;
//				if (record.getInt("goodsType")==1) {
//					String sql="";
//					if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
//						sql="SELECT "
//							+ "tg.thumb,tg.name,tg.id,tg.shortInfo,tg.sellPrice,tg.categoryonid, "
//							+ "tg.categorytwid,tg.categorythid,tg.categoryfoid,ts.saleprice "
//							+ " ,(SELECT IFNULL(SUM(bol.count),0 ) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY+") saleCount "
//							
//							+ "FROM  t_bus_goods tg "
//							+ "LEFT JOIN (SELECT t.saleprice,t.goodsId FROM t_bus_goods_sale t GROUP BY t.goodsId) ts ON ts.goodsId=tg.id "
//							+ "WHERE 1=1 AND tg.groupbuying=? AND tg.id=? AND tg.state=2 "
//							+ "AND tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ";
//						 
//						 r= Db.findFirst(sql, GoodCode.ISGROUP.NOTGROUP, record.getInt("fkey"),storeid);
//					}else {
//						sql = "SELECT "
//								+ "tg.thumb,tg.name,tg.id,tg.shortInfo,tg.sellPrice,tg.categoryonid, "
//								+ "tg.categorytwid,tg.categorythid,tg.categoryfoid,ts.saleprice "
//								+ " ,(SELECT IFNULL(SUM(bol.count),0 ) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY+") saleCount "
//								
//								+ "FROM  t_bus_goods tg "
//								+ "LEFT JOIN (SELECT t.saleprice,t.goodsId FROM t_bus_goods_sale t GROUP BY t.goodsId) ts ON ts.goodsId=tg.id "
//								+ "WHERE 1=1 AND tg.groupbuying=? AND tg.id=? AND tg.state=2";
//						
//						r = Db.findFirst(sql , GoodCode.ISGROUP.NOTGROUP, record.getInt("fkey"));
//					}
//				}else {
//					String sql="SELECT tg.thumb,tg.name,tg.shortInfo,tg.categoryonid, tg.categorytwid,tg.categorythid,tg.categoryfoid,tgs.* "
//							+ " ,(SELECT IFNULL(SUM(bol.count),0 ) FROM t_bus_order_act bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=tg.id AND bo.payState="+OrderCode.OrderPayState.HAD_PAY+") saleCount "
//						
//							+ "FROM  t_bus_ege_goods tg  LEFT JOIN t_bus_ege_goods_spec tgs ON tgs.goodId=tg.id  "
//							+ "WHERE 1=1  AND tg.id=? AND tg.state=2 "
//							+ "AND tgs.id = (select min(id) from t_bus_ege_goods_spec tgs  where tgs.goodId=?)";
//					r = Db.findFirst(sql , record.getInt("fkey"), record.getInt("fkey"));
//				}
//				
//				if (r!=null) {
//					record.setColumns(r);
//					list1.add(record);
//				} 
//			}
		}
	
		return list1;
	}
	/**
	 * 查询秒杀信息
	 * @param object
	 */
	public Record findSpike(int  id) {
		return Db.findFirst("SELECT id as Spikeid,listId,spikePrice,spikeNum,spikeLeft,limited FROM t_spike_union WHERE goodsId=?",id);
	}

	/**
	  * 查询购物车信息
	  * @param cids
	  */
	 public List<Record> findSaleByCids(String cids,String storeid) {
	  String sql = "SELECT ts.id,ts.goodsid,ts.title,ts.goodsSpec,ts.saleprice,ts.`salestock`, "
	    + "tg.thumb,tg.zsactivity,tg.zspointnum,tg.zsdedunum,tg.zscouponnum,tg.state "
	    + "FROM t_bus_goods_sale ts "
	    + "LEFT JOIN t_bus_goods tg ON tg.id=ts.goodsid "
	    + "WHERE ts.id IN ("+cids+")";
	  if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
		  sql="SELECT ts.id,ts.goodsid,ts.title,ts.goodsSpec,ts.saleprice,ts.`salestock`, "
				    + "tg.thumb,tg.zsactivity,tg.zspointnum,tg.zsdedunum,tg.zscouponnum,tg.state "
				    + "FROM t_bus_goods_sale ts "
				    + "LEFT JOIN t_bus_goods tg ON tg.id=ts.goodsid "
				    + "WHERE ts.id IN ("+cids+")  "
				    + "AND tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ";
			
			return Db.find(sql,storeid);
		}
	  return Db.find(sql);
	 }
	/**
	 * 兑换商品页面查询
	 * @return
	 */
	public List<Record> findExchangeList(String lid) {
		String sql = "SELECT tg.id,tg.name,tg.thumb,tg.shortInfo,tg.pstate,tg.LunBoNum,tg.lableId, "
				+ "ts.specNum,ts.marketPrice,ts.stockNum,ts.multipay,ts.id AS sid, "
				+ "ts.opPoint,ts.opDedu,ts.opCoupon,ts.muchsellerPrice,ts.mpPoint,ts.mpDedu,ts.mpCoupon "
				+ "FROM t_bus_ege_goods tg "
				+ "LEFT JOIN t_bus_ege_goods_spec ts ON tg.id=ts.goodId "
				+ "WHERE tg.state=2  GROUP BY tg.id";
		return Db.find(sql);
	}
	/**
	 * 兑换商品详情
	 * @return
	 */
	public List<Record> findExchangeDetailList(String id) {
		String sql = "SELECT tg.id,tg.name,tg.thumb,tg.shortInfo,tg.pstate,tg.browse,tg.LunBoNum,tg.Qrthumb, "
				+ "ts.specNum,ts.marketPrice,ts.stockNum,ts.multipay,ts.id AS sid, "
				+ "ts.opPoint,ts.opDedu,ts.opCoupon,ts.muchsellerPrice,ts.mpPoint,ts.mpDedu,ts.mpCoupon "
				+ "FROM t_bus_ege_goods tg "
				+ "LEFT JOIN t_bus_ege_goods_spec ts ON tg.id=ts.goodId "
				+ "WHERE tg.state=2 AND tg.id = ?";
		return Db.find(sql,id);
	}
	/**
	 * 查询兑换商品图文详情
	 * @param goodsid 商品ID
	 */
	public List<Record> findExchangeDetailDescribe(int goodsid){
		return Db.find("SELECT gdi.thumb,gdi.orderNo FROM goods_details_images gdi , t_bus_ege_goods tg WHERE tg.LunBoNum=gdi.LunBoNum AND tg.id=? ORDER BY gdi.orderNo",goodsid);
	}
	/**
	 * 查找标签
	 * @return
	 */
	public Record findTitle(String id) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT `title`,id FROM t_bus_goods_title WHERE id=?",id);
	}
	/**
	 * 查找标签集合
	 */
	public List<Record> findTitleList() {
		// TODO Auto-generated method stub
		return Db.find("SELECT `title`,id FROM t_bus_goods_title");
	}

	public List<Record> findCategory() {
		return Db.find("SELECT tc.* FROM t_dict_category tc");
	}
	
	/**
	 * 根据商品ID查询三级分类ID
	 * @param goodsid
	 * @return
	 */
	public Record queryGoodsCate(int goodsid) {
		String sql="select tg.categoryonid,tg.categorytwid,tg.categorythid from t_bus_goods tg where tg.id=?";
		return Db.findFirst(sql,goodsid);
	}
	
	/**
	 * 普通商品查询同类商品
	 * @param goodsid
	 * @return
	 */
	public List<Record> findOtherGoodsSamecate(int goodsid,String storeid){
		String sql="select min(tgs.saleprice) as price1,tg.name,tg.shortinfo,tg.thumb,tg.id "
				+ "from t_bus_goods_sale tgs ,t_bus_goods tg "
				+ "where tg.categorythid=(select tg.categorythid from t_bus_goods tg where tg.id=? ) "
				+ "and tg.id =tgs.goodsid "
				+ "and tg.id!=? "
				+ "and tg.state=2 "
				+ "group by tg.id order by tg.id DESC limit 10";	
		if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
			sql="select min(tgs.saleprice) as price1,tg.name,tg.shortinfo,tg.thumb,tg.id "
					+ "from t_bus_goods_sale tgs ,t_bus_goods tg "
					+ "where tg.categorythid=(select tg.categorythid from t_bus_goods tg where tg.id=? ) "
					+ "and tg.id =tgs.goodsid "
					+ "and tg.id!=? "
					+ "and tg.state=2 "
					+ "and tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? )  "
					+ "group by tg.id order by tg.id DESC limit 10";
			return Db.find(sql,goodsid,goodsid,storeid);
		}
		return Db.find(sql, goodsid,goodsid);
	}
	
	/**
	 * 兑换商品查询同类商品
	 * @param goodsid
	 * @return
	 */
	public List<Record> findOtherGoodsSamecateEge(int goodsid) {
		String sql="select min(tgs.saleprice) as price1,tg.name,tg.shortinfo,tg.thumb,tg.id  "
				+ "from t_bus_goods_sale tgs ,t_bus_goods tg "
				+ "where tg.categorythid=(select tg1.categorythid from t_bus_ege_goods tg1 where tg1.id=? ) "
				+ "and tg.id =tgs.goodsid  "
				+ "and tg.state=2 "
				+ "group by tg.id order by tg.id DESC limit 10";
		
		return Db.find(sql, goodsid);
	}
	
	
	/**
	 * 更新商品浏览量
	 * @param goodsidStr
	 */
	public int updateLookByid(String goodsidStr) {
		return Db.update("UPDATE t_bus_goods SET browse=browse+? WHERE id=?",1, goodsidStr);
	}
	/**
	 * 更新商品浏览量
	 * @param goodsidStr
	 */
	public int updateLookByidExchange(String id) {
		return Db.update("UPDATE t_bus_ege_goods SET browse=browse+? WHERE id=?",1, id);
	}
	
	public List<Record> findCommentById(String id,String isExchange,String storeid) {
		// TODO Auto-generated method stub
		String sql = null;
		if("1".equals(isExchange)){
			sql = "SELECT te.userid,te.eval1,te.content,te.createDate,te.LunBoNum, "
					+ "te.flag,tg.name FROM t_bus_ege_goods_spec ts "
					+ "RIGHT JOIN t_bus_order_eval te ON ts.id = te.specid "
					+ "LEFT JOIN t_bus_ege_goods tg ON ts.goodId = tg.id "
					+ "WHERE ts.`goodId` = ? AND te.orderNum like 'D%' ";
		}else if ("2".equals(isExchange)){
			sql = "SELECT te.userid,te.eval1,te.content,te.createDate,te.LunBoNum, "
					+ "te.flag,tg.name,tg.shortInfo as title FROM t_bus_goods tg "
					+ "RIGHT JOIN t_bus_order_eval te ON tg.id = te.specid "
					+ "WHERE tg.id = ? AND te.orderNum like 'T%' ";
		}else{
			sql = "SELECT te.userid,te.eval1,te.content,te.createDate,te.LunBoNum, "
					+ "te.flag,ts.`title` FROM t_bus_goods_sale ts "
					+ "RIGHT JOIN t_bus_order_eval te ON ts.id = te.specid "
					+ "WHERE ts.goodsid = ? AND te.orderNum like 'G%' ";
			if (!StrKit.isBlank(storeid)&&!"undefined".equals(storeid)) {
				sql = "SELECT te.userid,te.eval1,te.content,te.createDate,te.LunBoNum, "
						+ "te.flag,ts.`title` FROM t_bus_goods_sale ts "
						+ "RIGHT JOIN t_bus_order_eval te ON ts.id = te.specid left join t_bus_goods tg on tg.id=? "
						+ "WHERE ts.goodsid = ? "
						+ "AND te.orderNum like 'G%'  "
						+ "AND tg.good_store in (select tgs.goodstore from t_bus_goods_store_rel tgs where tgs.storeid = ? ) ";
				
				return Db.find(sql,id, id,storeid);
			}
		}
		return Db.find(sql, id);
	}
	/**
	 * 查找评论图片
	 * @param LunBoNum
	 * @return 
	 */
	public List<Record> findImageByLunBoNum(String LunBoNum) {
		// TODO Auto-generated method stub
		return Db.find("SELECT * FROM t_bus_order_eval_images WHERE LunBoNum=? ", LunBoNum);
	}
}
